﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DBBase;
using System.IO;
using System.Reflection; // 引用这个才能使用Missing字段
using System.Xml;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;
using FastReport;


namespace Business
{
    public partial class vehicledata : Form
    {
        public string slsh = "";
        public string jclb = "";
        public string shpzl = "";
        public string szs = "";
        Query objquery = new Query();
        public vehicledata()
        {
            InitializeComponent();
            getjilu();
            inicbx(comboBox3, "table_hpzl");
            inicbx(comboBox1, "table_jclb");
        }
        public MainForm m_mainform;
        public void getjilu()
        {
            lv.Items.Clear();
            Query mquery = new Query();
            string sql1;
            string sql2;
            string sql3;
            string sql = string.Format(@"SELECT AUTO.*,Detects_SJA.*,Detects_SJB.* from AUTO,Detects_SJA,Detects_SJB where AUTO.AT_ID=Detects_SJA.AT_ID and Detects_SJB.DETECT_ID=Detects_SJA.DETECT_ID");
            sql = sql + string.Format(@" and Detects_SJA.DET_DATE>'{0}' and Detects_SJA.DET_DATE<'{1}'", date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");
            if (comboBox3.Text.Trim() != "")
            {
                sql = sql + string.Format(@" and AUTO.AT_HPZL like '%{0}%'", comboBox3.Text.Trim());
            }
            if (textBox4.Text.Trim() != "")
            {
                sql = sql + string.Format(@" and AUTO.AT_SYR like '%{0}%'", textBox4.Text.Trim());
            }

            if (comboBox2.Text.Contains("初检"))
            {
                sql = sql + "and Detects_SJA.DET_JYCS=1";
            }
            else if (comboBox2.Text.Contains("复检"))
            {
                sql = sql + "and Detects_SJA.DET_JYCS>1";
            }
            if (comboBox1.Text.Trim() != "")
            {
                sql = sql + string.Format(@" and Detects_SJA.DET_JCLB = '{0}'", comboBox1.Text.Trim());
            }



            sql1 = sql + " order by Detects_SJA.DETECT_ID desc ";

            DataTable dt = mquery.GetTable(sql1);

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ListViewItem lt = new ListViewItem(dt.Rows[i]["DETECT_ID"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["AT_HPHM"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["AT_HPZL"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_JCLB"].ToString().Trim());
                    if (dt.Rows[i]["DET_JCLB"].ToString().Trim().Contains("注册"))
                    {
                        lt.BackColor = Color.Red;
                    }
                    lt.SubItems.Add(dt.Rows[i]["AT_SYR"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_DATE"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_JYCS"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["AT_CCDJRQ"].ToString().Trim().Contains("1900") ? "" : dt.Rows[i]["AT_CCDJRQ"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["AT_DH"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["AT_HDZK"].ToString().Trim());
                    lt.SubItems.Add(dt.Rows[i]["DET_YCY"].ToString().Trim());
                    this.lv.Items.Add(lt);
                }
                if (comboBox2.Text.Contains("初检"))
                {
                    textBox1.Text = dt.Rows.Count.ToString();
                    textBox2.Text = "0";
                    textBox3.Text = dt.Rows.Count.ToString();
                }
                else if (comboBox2.Text.Contains("复检"))
                {
                    textBox1.Text = "0";
                    textBox2.Text = dt.Rows.Count.ToString();
                    textBox3.Text = dt.Rows.Count.ToString();
                }
                else
                {
                    textBox3.Text = dt.Rows.Count.ToString();

                    sql2 = sql + "and Detects_SJA.DET_JYCS=1";
                    dt = mquery.GetTable(sql2);
                    textBox1.Text = dt.Rows.Count.ToString();

                    sql3 = sql + "and Detects_SJA.DET_JYCS>1";
                    dt = mquery.GetTable(sql3);
                    textBox2.Text = dt.Rows.Count.ToString();
                }
            }
        }
        private void skinButton1_Click(object sender, EventArgs e)
        {
            getjilu();
        }
        private void but_daochu_Click(object sender, EventArgs e)
        {
            string path = "";
            path = Application.StartupPath + path + DateTime.Now.ToString("yyyyMMdd") + ".xls";
            excel(path);
        }
        private void excel(string path)
        {
            try
            {
                StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();

                sb.Append("统计时间从" + date_kaishi.Value.ToShortDateString() + "至" + date_jieshu.Value.ToShortDateString() + "\t");
                sb.Append(Environment.NewLine);
                for (int i = 0; i < lv.Columns.Count; i++)
                    sb.Append(lv.Columns[i].Text.ToString() + "\t");
                sb.Append(Environment.NewLine);

                foreach (ListViewItem li in lv.Items)
                {
                    for (int i = 0; i < lv.Columns.Count; i++)
                        sb.Append(li.SubItems[i].Text + "\t");
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                MessageBox.Show("成功保存于" + path + "文件！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        void inicbx(ComboBox cbx, string dataname)
        {
            string ssqlex = "";
            ssqlex = string.Format(@"select * from {0} order by table_1", dataname);
            DataTable dt = objquery.GetTable(ssqlex);
            cbx.Items.Add("");
            cbx.Text = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cbx.Items.Add(dt.Rows[i]["table_2"].ToString().Trim());
            }
        }

        private void lv_MouseUp(object sender, MouseEventArgs e)
        {
            slsh = "";
            jclb = "";
            shpzl = "";
            szs = "";
            foreach (ListViewItem item in lv.SelectedItems)
            {
                slsh = item.SubItems[0].Text.ToString().Trim();
                jclb = item.SubItems[3].Text.ToString().Trim();
                shpzl = item.SubItems[2].Text.ToString().Trim();
            }
            string sql = string.Format(@"select AUTO.AT_CLLX,AUTO.AT_ZS FROM AUTO,Detects_SJA where AUTO.AT_ID=Detects_SJA.AT_ID and Detects_SJA.DETECT_ID='{0}'", slsh);
            //scllx = mqury.GetDBString(sql, "AT_CLLX");
            szs = objquery.GetDBString(sql, "AT_ZS");
        }

    }
}
